<?php

/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
*/
/**
 * Description of UserQuestion
 *
 * @author hoangle
 */
class UserQuestion {

    private $db;
    
    public function  __construct() {
        $this->db = Zend_Registry::get('db');
    }

    /**
     * @desc Get user question
     * @param <array> $arrParams (userid)
     * @return <array> data else NULL
     */
    public function getUserSetQuestion($arrParams) {
        try {
            $stmt = $this->db->prepare('CALL sp_getUserSetQuestion(:p_userid);');
            $stmt->bindValue('p_userid', $arrParams['userid']);
            $stmt->execute();
            $rows = $stmt->fetchAll();
            $stmt->closeCursor();
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e);
        }
        return $rows;
    }

    /**
     * @desc Get list question of user
     * @return <type>
     */
    public function getQuestionUser() {
        try {
            $sql = "SELECT count(mackid) as amount, mackid FROM tbluserquestions WHERE status=1
                AND answer_flag=0 AND date_format(createtime, '%d')=date_format(now(), '%d') GROUP BY mackid";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $rows = $stmt->fetchAll();
            $stmt->closeCursor();
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e);
        }
        return $rows;        
    }

    /**
     * @desc Get user question
     * @param <array> $arrParams (userid)
     * @return <array> data else NULL
     */
    public function getQuestionInfoByUser($arrParams) {
        try {
            $stmt = $this->db->prepare('CALL sp_getUserSetQuestion(:p_userid);');
            $stmt->bindValue('p_userid', $arrParams['userid']);
            $stmt->execute();
            $rows = $stmt->fetchAll();
            $stmt->closeCursor();
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e);
        }
        return $rows;
    }

    /**
     * @desc Get user question
     * @param <array> $arrParams (userid)
     * @return <array> data else NULL
     */
    public function replyAnswer($arrParams) {
        try {
            $sql = "UPDATE tbluserquestions SET answer=:answer, answer_flag=1, refuse_flag=:refuse_flag
                WHERE mackid=:mackid AND status=1 AND answer_flag=0 AND date_format(createtime, '%d')=date_format(now(), '%d') ";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':answer', $arrParams['answer'], PDO::PARAM_STR);
            $stmt->bindParam(':refuse_flag', $arrParams['refuse_flag'], PDO::PARAM_INT);
            $stmt->bindParam(':mackid', $arrParams['mackid'], PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();
            unset($stmt);
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e);
        }
        return true;
    }


    /**
     * @desc Get list user question by userid
     * @param <type> $userid
     * @return <array> data user question
     */
    public function getAnswerRefuse() {
        try {
            $sql = "SELECT * FROM tbluserquestions WHERE status=1 AND answer_flag=1 
                AND refuse_flag=2 AND admin_refuse=0 ORDER BY createtime DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetchAll();
            $stmt->closeCursor();
            unset($stmt);
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e->getMessage(), 11);
        }
        return $result;
    }

    /**
     * @desc Get list user question by userid
     * @param <type> $userid
     * @return <array> data user question
     */
    public function getUserQuestionAll() {
        try {
            $sql = "SELECT * FROM tbluserquestions WHERE status=1 AND answer_flag=0
                AND refuse=0 AND date_format(createtime, '%Y-%m-%d')= date_format(now(), '%Y-%m-%d')
                ORDER BY createtime DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetchAll();
            $stmt->closeCursor();
            unset($stmt);
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e->getMessage(), 11);
        }
        return $result;
    }

    /**
     * @desc Get user question
     * @param <array> $arrParams (userid)
     * @return <array> data else NULL
     */
    public function allowRefundAnswer($arrParams) {
        try {
            $sql = "UPDATE tbluserquestions SET refuse_flag=:refuse_flag
                WHERE mackid=:mackid AND status=1 AND answer_flag=1 AND refuse_flag=2";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':refuse_flag', $arrParams['refuse_flag'], PDO::PARAM_INT);
            $stmt->bindParam(':mackid', $arrParams['mackid'], PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();
            unset($stmt);
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e);
        }
        return true;
    }

    /**
     * @desc Get list user question by userid
     * @param <type> $userid
     * @return <array> data user question
     */
    public function getAutoRefund() {
        try {
            $sql = "SELECT * FROM tbluserquestions WHERE status=1 AND answer_flag=1
                AND refuse_flag=10 AND admin_refuse=0 ORDER BY createtime DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetchAll();
            $stmt->closeCursor();
            unset($stmt);
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e->getMessage(), 11);
        }
        return $result;
    }

    /**
     * @desc Decrease balance of user
     * @param <type> $arrData
     * @return <type>
     */
    public function creaseCreditByUserId($userid) {
        try {
            $sql = "UPDATE tblbalances SET totalcredit=totalcredit + 1 WHERE userid=:p_userid";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':p_userid', $userid, PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();
            unset($stmt);
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e);
        }
        return true;
    }

    /**
     * @desc Get user question
     * @param <array> $arrParams (userid)
     * @return <array> data else NULL
     */
    public function updateRefuseFlag($id) {
        try {
            $sql = "UPDATE tbluserquestions SET refuse_flag=0, admin_refuse=1,
                refuse=0, refuse_date=NOW(), count_refuse=1
                WHERE id=:id AND status=1 AND refuse_flag=10";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':id', $id, PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();
            unset($stmt);
        }
        catch (Zend_Db_Exception $e) {
            throw new Zend_Exception($e);
        }
        return true;
    }


}

?>
